SingleStore Operational Queries

Essential queries for SingleStore database operations and maintenance.

Version Information

# MySQL protocol compatible version
SELECT @@version;

# SingleStore actual engine version, the one we care about
select @@memsql_version;

Character Set and Collation

# Shows server default character set and collation
SELECT @@character_set_server, @@collation_server;

Auto-increment Synchronization

Fixes auto-increment counters that are lower than current values.

AGGREGATOR SYNC AUTO_INCREMENT ON farfalla ALL;

Memory Analysis

Collects memory usage details from past query runs across leaf nodes.

ANALYZE MEMORY;

Show Projections

Lists all projections (indexed views) in the database.

SHOW PROJECTIONS;

Process Monitoring

# Shows currently running queries
SHOW PROCESSLIST;

# Detailed process information from information schema
SELECT * FROM information_schema.mv_processlist;

Cluster Topology

Shows all leaf nodes in the cluster.

SHOW LEAVES;

Bulk Database Operations

Generate DROP statements for multiple databases:

-- Drop all coniglio databases
SELECT CONCAT('DROP DATABASE `', SCHEMA_NAME, '`;')
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME LIKE 'coniglio%';

-- Drop all farfalla databases
SELECT CONCAT('DROP DATABASE `', SCHEMA_NAME, '`;')
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME LIKE 'farfalla%';

-- Example drop command
DROP DATABASE `coniglio_tests`;

Projection Management

-- List projections
SHOW PROJECTIONS;

-- Drop specific projections
DROP PROJECTION coupons_by_created_at ON coupons;
DROP PROJECTION taxables_by_issue_id_test ON taxables;
DROP PROJECTION taxables_by_issue_id ON taxables;